Real MySQL 8.0 (3)

ordilov / 2022. 3. 4

트랜잭션은 작업의 완전성을 보장해줍니다. 논리적이 작업 셋을 모두 완벽하게 처리하거나, 처리하지 못할 경우 복구합니다. 잠금은 동시성을 제어하기 위한 기능이고 트랜잭션은 데이터의 정합성을 보장합니다. 여러 커넥션에서 동시에 한 데이터를 수정하면 값은 예측할 수 없어집니다. 잠금으로 한 시점에 한 커넥션만 변경할 수 있는 역할을 담당합니다. 격리 수준은 트랜잭션 내에서 작업 내용 공유와 차단 수준을 결정합니다.

MyISAM이나 MEMORY 엔진은 트랜잭션을 보장하지 않으며 InnoDB 엔진은 지원합니다. 트랜잭션을 보장하지 않는 경우 정합성을 보장하기 힘든 예제입니다.

CREATE TABLE tab_myisam (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=MyISAM;
INSERT INTO tab_myisam (id) VALUES(3);

SET AUTOCOMMIT=ON;
INSERT INTO tab_myisam (id) VALUES (1), (2), (3);

InnoDB 엔진이라면 다음 상황에서 테이블에 남는 값은 3이 됩니다. 하지만 MyISAM의 경우 중간에 키 중복 오류로 쿼리가 실패해도 1, 2가 남아있어 1, 2, 3이 남아있습니다. 이렇게 부분 업데이트가 발생하면 원치 않는 값들이 들어갈 수 있습니다. 따라서 남은 쿼리를 삭제하는 재처리 작업이 필요할 수 있습니다. IF, ELSE로 로직을 분리할 수 있지만 마찬가지로 재처리 작업으로 지저분해집니다.

트랜잭션은 DBMS 커넥션처럼 필요한 최소의 코드에만 적용해 범위를 최소화하는 것이 좋습니다. 트랜잭션을 시작한 상태에서 미리 읽어올 수 있던 데이터를 읽어오거나, 메일을 발송하는 등 외부 처리는 피하는 것이 좋습니다.

MySQL에서 사용되는 잠금은 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있습니다. MySQL 엔진은 스토리지 엔진을 제외한 나머지 부분으로 모든 스토리지 엔진에 영향을 미칩니다. MySQL 엔진에서는 테이블 데이터 동기화를 위한 테이블 락, 메타데이터 락, 네임드 락을 제공합니다.

글로벌 락은 FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있습니다. MySQL에서 제공하는 잠금 중 가장 범위가 크며 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외하면 대기 상태가 됩니다. 글로벌 락이 미치는 영향은 MySQL 서버 전체로 작업 대상 테이블이나 데이터베이스가 다르더라도 동일한 영향을 미칩니다. MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때 글로벌 락을 사용합니다. 글로벌 락이 실행되기 전에 이전 모든 명령이 끝나야 실행되며, 락으로 다른 명령이 오랫동안 실행되지 못할 수 있습니다. 따라서 웹 서비스용 서버에는 가급적 사용하지 않는 것이 좋습니다.

InnoDB에서는 트랜잭션을 지원하기 때문에 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요가 없습니다. 그래서 좀 더 가벼운 글로벌 락을 사용할 수 있습니다.

LOCK INSTANCE FOR BACKUP;

UNLOCK INSTANCE;

특정 세션에서 백업 락을 획득하면 테이블 스키마나 사용자 인증 관련 정보만 변경할 수 없습니다. 일반적인 테이블의 데이터 변경은 사용 가능합니다. 백업은 주로 레플리카 서버에서 실행되는데 도중에 스키마 변경이 일어나면 백업이 실패해 백업 락은 이를 막습니다.

개별 테이블 단위로 설정되는 잠금으로 명시적, 묵시적으로 LOCK TABLES table_name [READ | WRITE]로 테이블 락을 획득할 수 있습니다. 테이블 락은 MyISAM, InnoDB 모두 동일하게 설정할 수 있습니다. 잠금 해제는 UNLOCK TABLES` 명령으로 반납합니다. 특별한 상황 외에는 온라인 작업에 상당한 영향을 끼쳐 사용하지 않습니다.

InnoDB의 경우 데이터 변경 시에는 무시되고 스키마를 변경하는 경우에만 영향을 미칩니다.

GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정하며 데이터베이스 객체가 대상이 아닙니다. 네임드 락은 단순히 사용자가 지정한 문자열을 획득하고 반납하는 잠금입니다. 여러 서버에서 DB에 어떤 정보를 동기화를 하는 경우 락을 통해 접근 가능한지 확인할 수 있습니다.

SELECT GET_LOCK('mylock', 2);

SELECT IS_FREE_LOCK('mylock');

SELECT RELEASE_LOCK('mylock');

SELECT RELEASE_ALL_LOCKS();

메타데이터 락은 데이터베이스 객체의 이름이나 구조등을 변경하는 경우에 자동으로 획득하는 잠금입니다. RENAME TABLE 같은 명령을 사용하는 경우 원본 이름과 변경하는 이름 모두 잠금을 설정합니다.

레코드 기반의 잠금 방식으로 MyISAM 보다 동시성 처리에서 뛰어나지만 MySLQ 명령으로 잠금 정보를 얻기 까다롭습니다. 기존에는 lock_monitor, SHOW ENGINE INNODB STATUS등으로 파악하는 명령이 전부였습니다. 최근에는 잠금과 대기 중인 목록을 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS를 조인해 조회할 수 있습니다.

레코드 단위로 락이 걸리며 레코드와 레코드 사이 간격을 잠그는 갭락이 존재합니다.

레코드 락

레코드 자체만을 잠그며 InnoDB의 차이점은 레코드가 아닌 인덱스의 레코드를 잠급니다. 인덱스가 하나도 없는 테이블이더라도 내부적으로 생성된 클러스터 인덱스를 이용해 잠금을 설정합니다.

갭 락

레코드와 인접한 레코드 사이의 간격만을 잠그며 그 사이의 간격에 새로운 레코드 생성을 제어합니다.

넥스트 키 락

레코드 락과 갭 락을 합쳐놓은 형태로 STATEMENT 포맷의 바이너리 로그에서는 REPEATABLE READ를 사용해야 합니다. 갭 락과 넥스트 키 락의 주 목적은 레플리카 서버에서 소스 서버에 바이너리 로그 쿼리를 동일하게 만들어내도록 보장합니다. 다만 이로 인해 데드락이나 다른 트랜잭션을 기다리게 만드는 일이 많으므로 되도록 로그를 ROW로 바꿔 락을 줄이는 것이 좋습니다.

자동 증가 락

AUTO_INCREMENT 칼럼 속성인 경우 동시에 여러 레코드가 INSERT되는 경우 중복되지 않고 순서대로 증가해야 합니다. 이를 위해 내부적으로 자동증가락으로 테이블 수준의 잠금을 제공합니다.

새로운 레코드 생성시에만 필요하므로 UPDATE, DELETE 등의 쿼리에는 걸리지 않습니다. 트랜잭션과 상관없이 INSERT나 REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다가 해제됩니다. 명시적으로 설정할 수 있는 방법은 없으며 시간이 아주 짧기 때문에 대부분 문제가 되지 않습니다.

InnoDB는 레코드가 아닌 인덱스를 잠그는 방식으로 변경 레코드를 찾기 위해 인덱스와 레코드 모두 락을 겁니다. 인덱스에 해당하는 개수가 많다면 그만큼의 레코드가 모두 락이 걸립니다. 인덱스가 없다면 풀 스캔으로 모든 테이블에 락을 걸게 되기 때문에 인덱스 설계가 중요합니다.

트랜잭션의 격리 수준이란 여러 트랜잭션이 동시에 처리될 때 다른 트랜잭션에서 변경이나 조회를 허용할지 결정하는 것입니다. "DIRTY READ"라고 하는 READ UNCOMMITTED는 일반적인 데이터베이스에서 잘 사용하지 않습니다. SERIALIZABLE은 동시성이 중요한 데이터베이스에서 거의 사용되지 않습니다. InnoDB에서는 REPEATABLE READ에서도 PHANTOM READ가 발생하지 않습니다.

트랜잭션의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보이는 경우입니다. 더티 리드 현상은 데이터가 나타났다가 사라졌다가 하는 현상을 초래해 사용자들을 혼란스럽게 만듭니다.

오라클 DBMS 에서 기본으로 사용되는 격리수준으로 가장 많이 사용되는 격리 수준입니다. 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 조회할 수 있습니다. 트랜잭션 내에서 테이블을 수정하는 중이더라도 다른 트랜잭션에서는 언두 로그에서 백업된 레코드를 가져옵니다. 생길 수 있는 문제는 NON-REPEATABLE READ가 불가능하다는 점이 있습니다. NON-REPEATABLE READ는 한 트랜잭션 내에서 초반에는 결과가 없다가 다른 트랜잭션 커밋 결과로 후반에 생기는 경우입니다. 이 경우 한 트랜잭션 내에서 같은 명령어가 같은 결과를 보장하지 않아 정합성이 깨집니다. 일반적인 경우에는 문제가 없지만 금전적인 처리처럼 동일 데이터를 여러번 읽으면 문제가 생깁니다.

SELECT 문도 트랜잭션 내에서 실행하면 트랜잭션 범위 내에서만 작동합니다. REPEATABLE READ 수준에선 다른 트랜잭션 밖에서 변경하고 COMMIT을 실행하더라도 항상 같은 값을 보게 됩니다.

InnoDB에서 기본으로 사용되는 격리 수준으로 바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ를 사용해야 합니다. 언두 로그에 기존 값을 백업해두고 동일한 트랜잭션 내에서는 동일한 결과를 보여줍니다. READ COMMITTED와의 차이점은 언두 영역에 백업된 레코드의 여러 버전 가운데 몇번째 이전까지 찾아 들어가느냐 입니다. REPEATABLE READ의 처리는 자신의 트랜잭션에서 처리한 언두 영역을 확인하는 것입니다. 한 트랜잭션에서 데이터를 확인하고 끝나지 않았을 때 다른 트랜잭션에서 데이터를 업데이트합니다. 다른 트랜잭션이 끝나고 아직 끝나지 않은 트랜잭션에서 값을 확인하면 언두 로그를 통해 같은 값을 확인할 수 있습니다.

모든 InnoDB 트랜잭션은 고유한 트랜잭션 번호를 가지며 언두 영역에 백업된 데이터는 불필요한 시점에 주기적으로 삭제됩니다. MVCC를 보장하기 위해서는 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 앞선 언두 영역은 삭제할 수 없습니다.

물론 이렇게 작업을 하다보면 언두 영역의 백업 데이터가 매우 커질 수 있어 처리 성능이 떨어질 수 있습니다. 그리고 생길 수 있는 부정합은 SELECT ... FOR UPDATE 쿼리 결과는 위에 상황일 때 서로 다르게 나옵니다. SELECT ... FOR UPDATE는 잠금을 걸게 되는데 언두 레코드는 잠금을 걸 수가 없습니다. 따라서 언두 레코드가 아닌 현재 레코드의 값을 가져와 다르게 보이게 되는 PHANTOM READ가 발생합니다.

가장 단순하면서 엄격한 격리 수준으로 동시 처리 성능은 떨어집니다. SERIALIZABLE은 읽기 작업도 공유 잠금이 되며 한 트랜잭션이 어떤 작업을 하든 다른 트랜잭션은 접근할 수 없습니다. InnoDB엔진은 REPEATABLE READ에서도 갭 락과 넥스트 키 락으로 PHANTOM READ를 막아 SERIALIZABLE의 필요성이 없습니다.